﻿/******************************************************   
创建者: ljj
创建时间: 2018-07-06
修改:
修改时间: 
功能: 店铺规划SKC
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using george.Data;
using NHibernateVertica;
using VerticaAPI.model;
using com.nm.library.Helper;
using Nancy.Extensions;

namespace VerticaAPI
{
    [Description("店铺规划SKC")]
    public class DWShopStandardSKCHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// writer:ljj
        /// Create Date：2018-07-06
        [MustLogin]
        [Description("店铺规划SKC列表")]
        [ReturnType(typeof(PaperData<List<DWShopStandardSKCModel.ListModel>>))]
        public ApiResult GetShopStandardSKCList([ParamDescription("关键字")] string Keys = "",
            [ParamDescription("省份")] string ProID = "", [ParamDescription("城市")] string CityID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var count = ReturnShopStandardSKCCount(companyID, extrSource, Keys, ProID, CityID);
                var result = PaperData<DWShopStandardSKCModel.ShowModel>.Create(count, Size, Page);
                if (count > 0)
                {
                    var query = ReturnShopStandardSKCList(companyID, extrSource, Keys, ProID, CityID, Page, Size).ToList();
                    var list = query.GroupBy(d => new {d.ShopCode, d.ShopName, d.StockID, d.CompanyID, d.ExtrSource})
                        .Select(d => new DWShopStandardSKCModel.ShowModel
                        {
                            CompanyID = d.Key.CompanyID,
                            ExtrSource = d.Key.ExtrSource,
                            ShopCode = d.Key.ShopCode,
                            ShopName = d.Key.ShopName,
                            StockID = d.Key.StockID,
                            kindList = d.GroupBy(k => new {k.KindID, k.KindName}).Select(
                                k => new DWShopStandardSKCModel.KindModel
                                {
                                    KindID = k.Key.KindID,
                                    KindName = k.Key.KindName,
                                    modelLength = k.Count(),
                                    modelList = k.GroupBy(m => new {m.ModelID, m.ModelName}).Select(
                                        m => new DWShopStandardSKCModel.ModelModel
                                        {
                                            ModelID = m.Key.ModelID,
                                            ModelName = m.Key.ModelName,
                                            itemLength = m.Count(),
                                            itemList = m.GroupBy(i => new {i.ItemID, i.ItemName}).Select(
                                                i => new DWShopStandardSKCModel.ItemModel
                                                {
                                                    ItemID = i.Key.ItemID,
                                                    ItemName = i.Key.ItemName,
                                                    subItemLength = i.Count(),
                                                    subItemList = i.Select(si => new DWShopStandardSKCModel.SubItemModel
                                                    {
                                                        SubItem = si.SubItem,
                                                        HaveSKC = si.HaveSKC,
                                                        StandardSKC = si.StandardSKC,
                                                    }).ToList(),
                                                }).ToList()
                                        }).ToList()
                                }).ToList(),
                        }).ToList();
                    result.Items = list;
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败!");
            }
        }

        /// <summary>
        /// list
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="ExtrSource"></param>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        public IList<DWShopStandardSKCModel.ListModel> ReturnShopStandardSKCList(string CompanyID, string ExtrSource,
            string Keys, string ProID, string CityID, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT DISTINCT table3.* ,
        skc.KindID ,
        kind.KindName ,
        skc.ModelID ,
        model.ModelName ,
        skc.ItemID ,
        item.ItemName ,
        skc.SubItem ,
        skc.HaveSKC ,
        skc.StandardSKC
FROM    ( SELECT    *
          FROM      ( SELECT    * ,
                                ROW_NUMBER() OVER ( ORDER BY ExtrSource, CompanyID, StockID, ShopCode ) AS Row
                      FROM      ( SELECT    skc.ExtrSource ,
                                            skc.CompanyID ,
                                            skc.StockID ,
                                            shop.ShopCode ,
                                            shop.ShopName
                                  FROM      ( SELECT    *
                                              FROM      ndw.ndw_ShopStandardSKC
                                              WHERE     ExtrSource = '{ExtrSource}'
                                                        AND CompanyID = '{CompanyID}'
                                            ) skc
                                            INNER JOIN ( SELECT *
                                                         FROM   ndw.ndw_Shop
                                                         WHERE  ExtrSource = '{ExtrSource}'
                                                                AND CompanyID = '{CompanyID}'
                                                                {(string.IsNullOrEmpty(CityID) ? "" : $"AND AreaID = '{CityID}'")}
                                                                {
                                    (string.IsNullOrEmpty(Keys)
                                        ? ""
                                        : $"AND ( ShopCode LIKE '%{Keys}%' OR ShopName LIKE '%{Keys}%' )")
                                }
                                                       ) shop ON skc.CompanyID = shop.CompanyID
                                                                 AND skc.ExtrSource = shop.ExtrSource
                                                                 AND skc.StockID = shop.StockID
                                            INNER JOIN ( SELECT  *
                                                        FROM    nods.nods_erp_Bas_Area
                                                        WHERE   ExtrSource = '{ExtrSource}'
                                                                {(string.IsNullOrEmpty(CityID) ? "" : $"AND AreaID = '{CityID}'")}
                                                                {(string.IsNullOrEmpty(ProID) ? "" : $"AND ParentID = '{ProID}'")}
                                                      ) area ON shop.AreaID = area.AreaID
                                                                AND skc.ExtrSource = area.ExtrSource
                                  GROUP BY  skc.ExtrSource ,
                                            skc.CompanyID ,
                                            skc.StockID ,
                                            shop.ShopCode ,
                                            shop.ShopName
                                ) table1
                    )table2
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table3
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_ShopStandardSKC
                    WHERE   ExtrSource = '{ExtrSource}'
                            AND CompanyID = '{CompanyID}'
                  ) skc ON table3.ExtrSource = skc.ExtrSource
                           AND table3.CompanyID = skc.CompanyID
                           AND table3.StockID = skc.StockID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Kind
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) kind ON skc.KindID = kind.KindID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Model
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) model ON skc.ModelID = model.ModelID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Item
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) item ON skc.ItemID = item.ItemID
ORDER BY table3.ExtrSource ,
        table3.CompanyID ,
        table3.StockID ,
        table3.ShopCode ,
        skc.KindID ,
        skc.ModelID ,
        skc.ItemID ,
        skc.SubItem";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<DWShopStandardSKCModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 条数
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="ExtrSource"></param>
        /// <param name="Keys"></param>
        /// <param name="ProID"></param>
        /// <param name="CityID"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        public int ReturnShopStandardSKCCount(string CompanyID, string ExtrSource, string Keys, string ProID,
            string CityID)
        {
            try
            {
                var sql = $@"SELECT    COUNT(1)
          FROM      ( SELECT    skc.ExtrSource ,
                                skc.CompanyID ,
                                skc.StockID ,
                                shop.ShopCode ,
                                shop.ShopName
                      FROM      ( SELECT    *
                                  FROM      ndw.ndw_ShopStandardSKC
                                  WHERE     ExtrSource = '{ExtrSource}'
                                            AND CompanyID = '{CompanyID}'
                                ) skc
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{ExtrSource}'
                                                    AND CompanyID = '{CompanyID}'
                                                    {(string.IsNullOrEmpty(CityID) ? "" : $"AND AreaID = '{CityID}'")}
                                                    {
                        (string.IsNullOrEmpty(Keys)
                            ? ""
                            : $"AND ( ShopCode LIKE '%{Keys}%' OR ShopName LIKE '%{Keys}%' )")
                    }
                                           ) shop ON skc.CompanyID = shop.CompanyID
                                                     AND skc.ExtrSource = shop.ExtrSource
                                                     AND skc.StockID = shop.StockID
                                INNER JOIN ( SELECT  *
                                            FROM    nods.nods_erp_Bas_Area
                                            WHERE   ExtrSource = '{ExtrSource}'
                                                    {(string.IsNullOrEmpty(CityID) ? "" : $"AND AreaID = '{CityID}'")}
                                                    {(string.IsNullOrEmpty(ProID) ? "" : $"AND ParentID = '{ProID}'")}
                                          ) area ON shop.AreaID = area.AreaID
                                                    AND skc.ExtrSource = area.ExtrSource
                      GROUP BY  skc.ExtrSource ,
                                skc.CompanyID ,
                                skc.StockID ,
                                shop.ShopCode ,
                                shop.ShopName
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        [JsonType]
        [MustLogin]
        [Description("修改店铺规划SKC")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult ModifyShopStandardSKC([ParamDescription("SKC")] DWShopStandardSKCModel.CreateModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空!");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var sql = $@"UPDATE  ndw.ndw_ShopStandardSKC
SET     StandardSKC = {param.StandardSKC}
WHERE   ExtrSource = '{extrSource}'
        AND CompanyID = '{companyID}'
        AND StockID = '{param.StockID}'
        AND KindID = '{param.KindID}'
        AND ModelID = '{param.ModelID}'
        AND ItemID = '{param.ItemID}'
        AND SubItem = '{param.SubItem}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                Helper.ExecuteNonQuery(cmd);
                return Success(0);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("保存失败!");
            }
        }

        /// <summary>
        /// export
        /// </summary>
        /// <param name="Keys"></param>
        /// <param name="KeyStr"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        public DataTable ExportShopStandardSKC(string Keys, string KeyStr)
        {
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                var sql = $@"SELECT  skc.* ,
        shop.ShopName ,
        shop.ShopCode ,
        kind.KindName ,
        model.ModelName ,
        item.ItemName
FROM    ( SELECT    *
          FROM      ndw.ndw_ShopStandardSKC
          WHERE     ExtrSource = '{extrSource}'
                    AND CompanyID = '{companyID}'
        ) skc
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_Shop
                     WHERE  ExtrSource = '{extrSource}'
                            AND CompanyID = '{companyID}'
                            AND ( ShopCode LIKE '%{KeyStr}%'
                                  OR ShopName LIKE '%{KeyStr}%'
                                )
                   ) shop ON skc.CompanyID = shop.CompanyID
                             AND skc.ExtrSource = shop.ExtrSource
                             AND skc.StockID = shop.StockID
        INNER JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{extrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND skc.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Kind
                    WHERE   ExtrSource = '{extrSource}'
                  ) kind ON skc.KindID = kind.KindID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Model
                    WHERE   ExtrSource = '{extrSource}'
                  ) model ON skc.ModelID = model.ModelID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Item
                    WHERE   ExtrSource = '{extrSource}'
                  ) item ON skc.ItemID = item.ItemID
WHERE 1 = 1
      {Keys}
ORDER BY skc.ExtrSource ,
        skc.CompanyID ,
        skc.StockID ,
        shop.ShopCode ,
        skc.KindID ,
        skc.ModelID ,
        skc.ItemID ,
        skc.SubItem";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <param name="dt"></param>
        /// writer:ljj
        /// Create Date：2018-07-06
        public void CreateTempTable(string TempTableName,DataTable dt)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    ShopName varchar(60),
    ShopCode varchar(60),
    KindName varchar(60),
    ModelName varchar(60),
    ItemName varchar(60),
    SubItem varchar(60),
    HaveSKC int ,
    StandardSKC int
);";
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// import
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        public DataTable ImportShopStandardSKC(DataTable dt)
        {
            var tempTableName = $" ndw.ntemp_{Guid.NewGuid():N}";
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                Helper.DropTempTable(tempTableName);
                CreateTempTable(tempTableName, dt);
                var list = ValidationData(companyID, extrSource, tempTableName);
                var newList = list.Where(d => !string.IsNullOrEmpty(d.StockID) && !string.IsNullOrEmpty(d.KindID) &&
                                              !string.IsNullOrEmpty(d.ModelID) && !string.IsNullOrEmpty(d.ItemID) &&
                                              !string.IsNullOrEmpty(d.ItemorSub));
                MergeShopStandardSKC(newList.Select(d => new DWShopStandardSKCModel.CreateModel
                {
                    ExtrSource = extrSource,
                    CompanyID = companyID,
                    StockID = d.StockID,
                    KindID = d.KindID,
                    ModelID = d.ModelID,
                    ItemID = d.ItemID,
                    SubItem = d.SubItem,
                    StandardSKC = d.StandardSKC,
                }).ToList());
                var errorList = list.Except(newList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                return errorDT;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw(ex);
            }
            finally
            {
                Helper.DropTempTable(tempTableName);
            }
        }

        /// <summary>
        /// 验证数据
        /// </summary>
        /// <param name="CompanyID"></param>
        /// <param name="ExtrSource"></param>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        /// writer:ljj
        /// Create Date：2018-07-06
        public IList<DWShopStandardSKCModel.ImportModel> ValidationData(string CompanyID, string ExtrSource, string TempTableName)
        {
            try
            {
                var sql = $@"SELECT  temp.* ,
        shop.StockID ,
        kind.KindID ,
        model.ModelID ,
        item.ItemID ,
        subItem.ItemorSub
FROM    {TempTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{ExtrSource}'
                            AND CompanyID = '{CompanyID}'
                  ) shop ON temp.ShopCode = shop.ShopCode
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Kind
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) kind ON temp.KindName = kind.KindName
                            AND shop.ExtrSource = kind.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Model
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) model ON temp.ModelName = model.ModelName
                             AND shop.ExtrSource = model.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Item
                    WHERE   ExtrSource = '{ExtrSource}'
                  ) item ON temp.ItemName = item.ItemName
                            AND shop.ExtrSource = model.ExtrSource
        LEFT JOIN ( SELECT  ExtrSource ,
                            CompanyID ,
                            SubItem AS ItemorSub
                    FROM    ndw.ndw_ShopStandardSKC
                    WHERE   ExtrSource = '{ExtrSource}'
                            AND CompanyID = '{CompanyID}'
                  ) subItem ON temp.SubItem = subItem.ItemorSub
                               AND shop.ExtrSource = subItem.ExtrSource
                               AND shop.CompanyID = subItem.CompanyID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<DWShopStandardSKCModel.ImportModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// merge
        /// </summary>
        /// <param name="list"></param>
        /// writer:ljj
        /// Create Date：2018-07-06
        public void MergeShopStandardSKC(IList<DWShopStandardSKCModel.CreateModel> list)
        {
            try
            {
                var tempTableName = $" ndw.ntemp_{Guid.NewGuid():N} ";
                const string targetTableName = @" ndw.ndw_ShopStandardSKC ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new
                        {
                            d.CompanyID,
                            d.ExtrSource,
                            d.StockID,
                            d.KindID,
                            d.ModelID,
                            d.ItemID,
                            d.SubItem
                        }));
                const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.StockID=sourceTable.StockID
         AND targetTable.KindID=sourceTable.KindID
         AND targetTable.ModelID=sourceTable.ModelID
         AND targetTable.ItemID=sourceTable.ItemID
         AND targetTable.SubItem=sourceTable.SubItem ";
                const string setConditions = @" StandardSKC = sourceTable.StandardSKC ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      StockID ,
                      KindID ,
                      ModelID ,
                      ItemID ,
                      SubItem ,
                      StandardSKC ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.StockID ,
                      sourceTable.KindID ,
                      sourceTable.ModelID ,
                      sourceTable.ItemID ,
                      sourceTable.SubItem ,
                      sourceTable.StandardSKC ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
